You are here: Learning the Basics > Settings > SQL Command Settings

SQL Command Settings

The applications make use of many SQL statements internally that are pre-defined, yet dynamic SQL query configurations. These "parameterized queries" are expected to contain specific parameters (@SomeParamName) in order to function. It is recommended that these queries do not get changed, unless a specific environment setting/configuration warrants a modification.

The most typical change that would be required to these statements is modifying the user/schema prefix for SDE-related objects - adding, updating or removing the prefix.

Example: ...sde.table_registry....
To...
dbo.table_registry...
Or...
table_registry...

Usage

  1. Click inside the text field and update the SQL statement as needed.
  2. Use the Reset button to reset all SQL statements back to the defaults.

Heads Up! It is possible that other SQL statements not listed, may also be reset.

Default SQL statements

Description SQL Statement
Column Filter: 
(^Shape$)|(^Shape.Length$)|(^Geometry$)
Copy Table: 
SELECT @Fields INTO @ToTable FROM @FromTable
Copy Table Oracle: 
CREATE TABLE @ToTable AS SELECT @Fields FROM @FromTable
Copy Table SQL Ce: 
CREATE TABLE @ToTable AS SELECT @Fields FROM @FromTable
Create Table: 
CREATE TABLE @TableValue (@ColumnDeclarations)
Date Query Formatter: 
'{0}'
Date Query Formatter Oracle: 
TO_DATE('{0}','YYYY-MM-DD HH24:MI:SS')
Delete Rows: 
DELETE FROM @TableValue WHERE @WhereClause
Drop Table: 
DROP TABLE @TableValue
Insert Row: 
INSERT INTO @TableValue (@ColumnNames) VALUES (@InsertValues)
Select Column Info: 
SELECT c.COLUMN_NAME, c.DATA_TYPE, c.IS_NULLABLE, 
	   c.CHARACTER_MAXIMUM_LENGTH, c.NUMERIC_PRECISION, 
	   c.NUMERIC_SCALE, c.ORDINAL_POSITION,
CASE WHEN pk.COLUMN_NAME IS NOT NULL THEN 1 ELSE 0 END AS PRIMARY_KEY
FROM INFORMATION_SCHEMA.COLUMNS c
LEFT JOIN (
	SELECT ku.TABLE_CATALOG,ku.TABLE_SCHEMA,ku.TABLE_NAME,ku.COLUMN_NAME
	FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc
	INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS ku
	ON tc.CONSTRAINT_TYPE = 'PRIMARY KEY' 
	AND tc.CONSTRAINT_NAME = ku.CONSTRAINT_NAME
	)   pk 
ON  c.TABLE_CATALOG = pk.TABLE_CATALOG
	AND c.TABLE_SCHEMA = pk.TABLE_SCHEMA
	AND c.TABLE_NAME = pk.TABLE_NAME
	AND c.COLUMN_NAME = pk.COLUMN_NAME
WHERE c.TABLE_NAME = @TableName AND c.TABLE_SCHEMA = @SchemaName
ORDER BY c.COLUMN_NAME
Select Column Info No Schema: 
SELECT c.COLUMN_NAME, c.DATA_TYPE, c.IS_NULLABLE, 
	   c.CHARACTER_MAXIMUM_LENGTH, c.NUMERIC_PRECISION, 
	   c.NUMERIC_SCALE, c.ORDINAL_POSITION,
CASE WHEN pk.COLUMN_NAME IS NOT NULL THEN 1 ELSE 0 END AS PRIMARY_KEY
FROM INFORMATION_SCHEMA.COLUMNS c
LEFT JOIN (
	SELECT ku.TABLE_CATALOG,ku.TABLE_SCHEMA,ku.TABLE_NAME,ku.COLUMN_NAME
	FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc
	INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS ku
	ON tc.CONSTRAINT_TYPE = 'PRIMARY KEY' 
	AND tc.CONSTRAINT_NAME = ku.CONSTRAINT_NAME
	)   pk 
ON  c.TABLE_CATALOG = pk.TABLE_CATALOG
	AND c.TABLE_SCHEMA = pk.TABLE_SCHEMA
	AND c.TABLE_NAME = pk.TABLE_NAME
	AND c.COLUMN_NAME = pk.COLUMN_NAME
WHERE c.TABLE_NAME = @TableName
ORDER BY c.COLUMN_NAME
Select Column Info No Schema Oracle: 
select c.COLUMN_NAME, c.DATA_TYPE, c.NULLABLE, 
	   c.DATA_LENGTH, c.DATA_PRECISION, 
	   c.DATA_SCALE, c.COLUMN_ID,
CASE WHEN i.COLUMN_NAME IS NOT NULL THEN 1 ELSE 0 END AS PRIMARY_KEY
from user_tab_columns c
LEFT JOIN (
	SELECT cols.COLUMN_NAME, cols.TABLE_NAME
	FROM all_constraints cons, all_cons_columns cols
	WHERE cols.table_name = UPPER(@TableName)
	AND cons.constraint_type in ('P')
	AND cons.constraint_name = cols.constraint_name
	AND cons.owner = cols.owner
	) i
ON c.TABLE_NAME = i.TABLE_NAME
	AND c.COLUMN_NAME = i.COLUMN_NAME
WHERE c.TABLE_NAME = UPPER(@TableName)
ORDER BY c.COLUMN_NAME
Select Column Info No Schema SQL Ce: 
select c.COLUMN_NAME, c.DATA_TYPE, c.IS_NULLABLE, 
	   c.CHARACTER_MAXIMUM_LENGTH, c.NUMERIC_PRECISION, 
	   c.NUMERIC_SCALE, i.ORDINAL_POSITION, i.PRIMARY_KEY 
from INFORMATION_SCHEMA.COLUMNS c
LEFT JOIN INFORMATION_SCHEMA.INDEXES i
ON c.TABLE_NAME = i.TABLE_NAME
	AND c.COLUMN_NAME = i.COLUMN_NAME
WHERE c.TABLE_NAME = @TableName
	AND c.TABLE_SCHEMA = @SchemaName
ORDER BY c.COLUMN_NAME
Select Column Info Oracle: 
select c.COLUMN_NAME, c.DATA_TYPE, c.NULLABLE, 
	   c.DATA_LENGTH, c.DATA_PRECISION, c.DATA_SCALE, c.COLUMN_ID,
CASE WHEN i.COLUMN_NAME IS NOT NULL THEN 1 ELSE 0 END AS PRIMARY_KEY
from all_tab_columns c
LEFT JOIN (
	SELECT cols.COLUMN_NAME, cols.TABLE_NAME
	FROM all_constraints cons, all_cons_columns cols
	WHERE cols.table_name = UPPER(@TableName)
	AND cons.constraint_type in ('P')
	AND cons.constraint_name = cols.constraint_name
	AND cons.owner = cols.owner
	) i
ON c.TABLE_NAME = i.TABLE_NAME
	AND c.COLUMN_NAME = i.COLUMN_NAME
WHERE c.TABLE_NAME = UPPER(@TableName)
	AND c.OWNER = @SchemaName
ORDER BY c.COLUMN_NAME
Select Column Info SQL Ce: 
select c.COLUMN_NAME, c.DATA_TYPE, c.IS_NULLABLE, 
	   c.CHARACTER_MAXIMUM_LENGTH, c.NUMERIC_PRECISION, 
	   c.NUMERIC_SCALE, i.ORDINAL_POSITION, i.PRIMARY_KEY 
from INFORMATION_SCHEMA.COLUMNS c
LEFT JOIN INFORMATION_SCHEMA.INDEXES i
ON c.TABLE_NAME = i.TABLE_NAME
	AND c.COLUMN_NAME = i.COLUMN_NAME
WHERE c.TABLE_NAME = @TableName
	AND c.TABLE_SCHEMA = @SchemaName
ORDER BY c.COLUMN_NAME
Select Count: 
SELECT COUNT(*) from @TableValue where @WhereClause
Select Distinct From Table: 
SELECT DISTINCT @Columns from @TableValue where @WhereClause
Select From Table: 
SELECT @Columns from @TableValue where @WhereClause
Select From Table Paged: 
SELECT @Columns from @TableValue where @WhereClause
                        ORDER BY @OrderBy OFFSET @OffsetRows ROWS FETCH NEXT @PageSize ROWS ONLY
Select From Table Paged Oracle: 
SELECT @Columns FROM (select @Columns, row_number()
                        OVER (order by @OrderBy)rn FROM @TableValue where @WhereClause
                        WHERE rn BETWEEN @StartRow AND @EndRow order by rn
Select Version View: 
SELECT imv_view_name FROM sde.SDE_table_registry WHERE table_name = @TableName
Select Version View Oracle: 
SELECT imv_view_name FROM sde.table_registry WHERE table_name = @TableName
Select Versions: 
SELECT name, owner, version_id, status, state_id, 
description, parent_name, parent_owner, parent_version_id, creation_time
                        FROM [sde].[SDE_versions]
Select Versions Oracle: 
SELECT NAME, OWNER, VERSION_ID, STATUS, STATE_ID, 
DESCRIPTION, PARENT_NAME, PARENT_OWNER, PARENT_VERSION_ID, CREATION_TIME
                        FROM sde.VERSIONS
Set Version Proc: 
sde.set_current_version
Set Version Proc Oracle: 
sde.VERSION_UTIL.set_current_version
Set Version Proc Param: 
version_name
Table Filter: |(^A[0-9])|(^D[0-9])|(^I[0-9])|(^KEYSET_)|(^REV_)|(^SDE_)|(IDX\$$)|(^GDB_)|(^DR\$)|(_evw$)|(^SDO_)|(^ST_)|(^SERVER_CONFIG$)|(^TABLE_LOCKS$)|(^DBTUNE$)|(^COLUMN_REGISTRY$)|(^GEOMETRY_COLUMNS$)|(^INSTANCES$)|(^LAYER_LOCKS$)|(^LINEAGES_MODIFIED$)|(^LOCATORS$)|(^METADATA$)|(^MBTABLES_MODIFIED$)|(^LAYERS$)|(^LINEAGES_MODIFIED$)|(^OBJECT_LOCKS$)|(^PROCESS_INFORMATION$)|(^RASTER_COLUMNS$)|(^SPATIAL_REFERENCES$)|(^STATE_LINEAGES$)|(^STATE_LOCKS$)|(^TABLE_REGISTRY$)|(^VERSION$)|(^VERSIONS$)
Table Type Filter: 
(^System$)
Truncate Table: 
TRUNCATE TABLE @TableValue
Update Rows: 
UPDATE @TableValue set @ColumnNames = @UpdateValue WHERE @WhereClause